Skip to main content

Data Backup

mysqldump​

  • mysqldump is a command-line utility provided by MySQL to export (backup) databases.

Why Use mysqldump?

  • To backup your database regularly (safety against crashes).
  • To migrate a database from one server to another.
  • To move data between environments (development → production).
  • To export specific tables or records for analysis.

General Syntax​

mysqldump -u [username] -p [database_name] > backup_file.sql
  • -u [username] → MySQL user
  • -p → Prompt for password (you’ll enter it after running the command)
  • [database_name] → The database to export
  • > backup_file.sql → Redirects output into a file

  1. Export Entire Database

    mysqldump -u root -p school > school_backup.sql

  2. Export Specific Tables

    mysqldump -u root -p school students teachers > school_partial_backup.sql

  3. Export Only Schema (No Data)

    mysqldump -u root -p --no-data school > school_schema.sql

  4. Export Only Data (No Schema)

    mysqldump -u root -p --no-create-info school > school_data.sql

  5. Export All Databases

    mysqldump -u root -p --all-databases > alldb_backup.sql

  6. Compressed Backup

    Since SQL dump files can be large, you can compress them:

    mysqldump -u root -p school | gzip > school_backup.sql.gz

Restoring from a Backup​

Importing with mysql Command-Line​

To restore a database from a dump file:

  1. First create the database (if not exists):

    CREATE DATABASE school;

  2. Then restore

    mysql -u root -p school < school_backup.sql

This runs all the SQL statements in school_backup.sql and rebuilds the database.

Importing with SOURCE Command​

  1. Log into MySQL with mysql -u root -p
  2. Select databse with USE school
  3. Run import SOURCE /path/to/school_backup.sql;

Importing via MySQL Workbench​

  1. Open MySQL Workbench
  2. Connect to your server
  3. Go to Server > Data Import
  4. Choose Import from Self-Contained File (your .sql backup)
  5. Select target schema (or create new one)
  6. Click Start Import

Importing CSV Data​

If you only have CSV data instead of .sql file, you can use:

LOAD DATA INFILE '/path/to/students.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, age);

Automating Backups​

Why Automate MySQL Backups?

  • Prevent data loss (server crashes, accidental deletes, corruption).
  • Ensure regular backups without human error.
  • Reduce downtime with ready-to-use restore files.
  • Keep historical backups (daily, weekly, monthly).

Ways to Automate Backups

  1. Linux (Cron Jobs) → Automate mysqldump on a schedule.
  2. Windows (Task Scheduler) → Run mysqldump periodically.
  3. Custom Shell Scripts / Batch Files → Add logic like compression, timestamps, cleanup.
  4. Third-Party Tools (Percona XtraBackup, phpMyAdmin export scheduling).

Automating Backups on Linux (Cron Jobs)​

Step 1: Create a backup script

Create a file /usr/local/bin/mysql_backup.sh:

#!/bin/bash
# MySQL Backup Script

USER="root"
PASSWORD="yourpassword"
DATABASE="school"
OUTPUT_DIR="/backups/mysql"
DATE=$(date +%F_%H-%M-%S)
FILENAME="$OUTPUT_DIR/${DATABASE}_backup_$DATE.sql.gz"

# Create backup directory if not exists
mkdir -p $OUTPUT_DIR

# Run mysqldump with gzip compression
mysqldump -u $USER -p$PASSWORD $DATABASE | gzip > $FILENAME

# Optional: Delete backups older than 7 days
find $OUTPUT_DIR -type f -name "*.gz" -mtime +7 -exec rm {} \;

Make it executable:

chmod +x /usr/local/bin/mysql_backup.sh

Step 2: Schedule with Cron

Open cron editor:

crontab -e

Add this line to run backup every day at 2 AM:

0 2 * * * /usr/local/bin/mysql_backup.sh

Result → Daily compressed backup files like:

school_backup_2025-08-24_02-00-00.sql.gz

Automating Backups on Windows (Task Scheduler)​

Step 1: Create a batch file mysql_backup.bat:

Place it in a secure directory (e.g., C:\scripts\).

@echo off
set USER=root
set PASSWORD=yourpassword
set DATABASE=school
set BACKUPDIR=C:\mysql_backups
set DATE=%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%_%TIME:~0,2%-%TIME:~3,2%

if not exist %BACKUPDIR% mkdir %BACKUPDIR%

mysqldump -u %USER% -p%PASSWORD% %DATABASE% > %BACKUPDIR%\%DATABASE%_backup_%DATE%.sql

echo Backup complete: %BACKUPDIR%\%DATABASE%_backup_%DATE%.sql

Step 2: Schedule Task

  1. Open Task Scheduler → Create Task
  2. Go to Triggers → Daily at 2:00 AM
  3. Go to Actions → Start a program → select mysql_backup.bat
  4. Save

Now backups will run automatically each night.

Automating Full Server Backups​

For all databases:

mysqldump -u root -p --all-databases | gzip > /backups/mysql/all_db_backup_$(date +%F).sql.gz

Or backup only schemas (structures):

mysqldump -u root -p --no-data --all-databases > alldb_schema.sql

Real-World Enhancements of Automatic Backups​

  • Compression → Use gzip/bzip2 to save space.
  • Rotation → Keep last 7 backups, auto-delete old ones.
  • Remote Storage → Upload backups to AWS S3, Google Drive, or FTP.
  • Monitoring → Send email alerts if backup fails.